using System;
using System.Collections.Specialized;
using System.Configuration;
using System.Data;
using System.Data.Common;
using System.Text;
using Kailua.net.windward.utils;
using net.windward.utils.ado.Oracle;
using NUnit.Framework;

namespace net.windward.utils.ado
{
	// bugbug - not testing SET or ENUM type.
	[TestFixture]
	public class TestOracleProvider
	{
		private string serverName;
		private string username;
		private string password;
		private string writeUsername;
		private string writePassword;

		private static readonly DbProviderFactory providerFactory;
		private static readonly string factoryClass;

		static TestOracleProvider()
		{
			// WrProviderFactories.ProviderInfo allProviders = WrProviderFactories.GetAllProviders();
			try
			{
				providerFactory = DbProviderFactories.GetFactory(WrOracleVendor.FactoryClass);
				factoryClass = WrOracleVendor.FactoryClass;
			}
			catch (Exception)
			{
				providerFactory = DbProviderFactories.GetFactory(WrOracleVendor.FactoryClassFramework);
				factoryClass = WrOracleVendor.FactoryClassFramework;
			}
		}

		[SetUp]
		public void Init()
		{
			NameValueCollection appSettings = ConfigurationManager.AppSettings;
			serverName = appSettings["OracleServer"];
			username = appSettings["OracleUsername"];
			password = appSettings["OraclePassword"];
			writeUsername = appSettings["OracleWriteUsername"];
			writePassword = appSettings["OracleWritePassword"];
		}

		[Test]
		public void TestCreateProvider()
		{

			WrOracleVendor vendor = (WrOracleVendor)WrProviderFactories.CreateProvider("Oracle name", null, factoryClass);
			Assert.IsNotNull(vendor);
			Assert.AreEqual(false, vendor.CanEnumerateDatabases);
// true only if TNSNAMES.ORA on client			Assert.AreEqual(true, vendor.CanEnumerateServers);
			Assert.AreEqual(false, vendor.CanLaunchAdministrator);
			Assert.AreEqual(false, vendor.CanRunScripts);
			Assert.AreEqual(false, vendor.CanUseTrustedConnection);
			Assert.AreEqual(WrVendor.DATABASE_MODE.NEVER, vendor.DatabaseMode);
			Assert.AreEqual(false, vendor.HasProviders);
			Assert.AreEqual("Oracle name", vendor.Name);
			Assert.AreEqual("Data Source=abc:1521/XE;Persist Security Info=True;User ID=123;Password=456",
				vendor.ConnectionString("abc:1521/XE", "", new WrCredentials("123", "456"), true));
		}

		[Test]
		public void TestHrEnumeration()
		{

			// enum databases, make sure has test one
			WrOracleServer server = new WrOracleServer(providerFactory, serverName);
			Assert.AreEqual(serverName, server.Name);
			IWrDatabase[] databases = server.GetDatabases(new WrCredentials(username, password));
			Assert.AreEqual(1, databases.Length);
			IWrDatabase db = databases[0];

			// 4 groups
			db.LoadMetadata(new WrCredentials(username, password), WrVendor.ELEM_OWNER.ALL, null);
			WrTable[] tables = db.Tables;
			Assert.AreEqual(62, tables.Length);

			db.LoadMetadata(new WrCredentials(username, password), WrVendor.ELEM_OWNER.SYSTEM, null);
			tables = db.Tables;
			Assert.AreEqual(48, tables.Length);

			db.LoadMetadata(new WrCredentials(username, password), WrVendor.ELEM_OWNER.USER_OWNED, null);
			tables = db.Tables;
			Assert.AreEqual(7, tables.Length);

			db.LoadMetadata(new WrCredentials(username, password), WrVendor.ELEM_OWNER.USER, null);
			tables = db.Tables;
			Assert.AreEqual(14, tables.Length);

			// HR tables
			db.LoadMetadata(new WrCredentials(username, password), WrVendor.ELEM_OWNER.USER, null);
			tables = db.Tables;
			Assert.AreEqual(14, tables.Length);
			Assert.AreEqual("COUNTRIES", tables[0].FullName);
			Assert.IsFalse(tables[0].IsSystem);
			Assert.AreEqual("REGIONS", tables[6].FullName);
			Assert.IsFalse(tables[6].IsSystem);

			// HR views
			WrView[] views = db.Views;
			Assert.AreEqual(17, views.Length);
			Assert.AreEqual("EMP_DETAILS_VIEW", views[0].FullName);
			Assert.IsFalse(views[0].IsSystem);

			// HR stored procedures
			WrStoredProcedure[] procs = db.StoredProcedures;
			Assert.AreEqual(0, procs.Length);

			// HR.EMPLOYEES columns
			Trap.trap(); // table index?
			WrColumn[] cols = db.Tables[4].Columns;
			Assert.AreEqual(11, cols.Length);
			Assert.AreEqual("EMPLOYEE_ID", cols[0].FullName);
			Assert.AreEqual(DbType.Int32, cols[0].DbType);
			Assert.AreEqual("DEPARTMENT_ID", cols[10].FullName);
			Assert.AreEqual(DbType.Int16, cols[10].DbType);


			// KailuaTest - all types
			db = new WrOracleDatabase(providerFactory, serverName);
			db.LoadMetadata(new WrCredentials(writeUsername, writePassword), WrVendor.ELEM_OWNER.USER, null);
			cols = db.Tables[0].Columns;
			Assert.AreEqual(14, cols.Length);
			// bugbug - assert each name & DbType. DbType is the key test
		}

		// bugbug - BINARY_FLOAT and BINARY_DOUBLE don't work
		// bugbug - is this every type?
		// note - cannot insert a timestamp
		private readonly string[] colNames = {"KNUMBER", "KVARCHAR", "KDATE", "KTIMESTAMP", "KCHAR", "KNVARCHAR", "KBLOB", "KCLOB",
		"KBINARY", "KBYTE", "KSHORT", "KDECIMAL" };
		private readonly object[] colValues = {1, "dave", DateTime.Now, DateTime.Now, "shirley", "lucky",  new byte[4], new byte[4],
		true, (byte)5, (Int16) 5643, new Decimal(123.45) };

		[Test]
		public void TestInsert()
		{

			using (WrConnection conn = new WrConnection("OracleWrite"))
			{
				conn.Open();

				// empty it to start the same
				using (WrCommand cmd = conn.CreateWrCommand())
				{
					cmd.CommandText = "delete from ALLTYPES";
					cmd.ExecuteNonQuery();
				}

				// test get the PK back
				long[] pk = new long[2];
				using (WrCommand cmd = conn.CreateWrCommand())
				{
					cmd.CommandText = "insert into AllTypes (kVarChar) values (?)";
					cmd.Set(0, "dave");
					pk[0] = cmd.ExecuteInsert("pk");
					Assert.IsTrue(pk[0] > 0);
				}

				using (WrCommand cmd = conn.CreateWrCommand())
				{
					cmd.CommandText = "insert into AllTypes (kVarChar) values (?)";
					cmd.Set(0, "shirley");
					pk[1] = cmd.ExecuteInsert("pk");
					Assert.IsTrue(pk[1] > pk[0]);
				}

				// test insert each var type
				for (int ind = 0; ind < colNames.Length; ind++)
				{
					using (WrCommand cmd = conn.CreateWrCommand())
					{
						cmd.CommandText = "insert into ALLTYPES (" + colNames[ind] + ") values (?)";
						cmd.Set(0, colValues[ind]);
						cmd.ExecuteInsert("pk");
					}
				}

				// write all together
				using (WrCommand cmd = conn.CreateWrCommand())
				{
					StringBuilder command = new StringBuilder("insert into ALLTYPES (");
					for (int ind = 0; ind < colNames.Length; ind++)
						command.Append(colNames[ind] + ",");
					command.Remove(command.Length - 1, 1);
					command.Append(") values (");
					for (int ind = 0; ind < colNames.Length; ind++)
						command.Append("?,");
					command.Remove(command.Length - 1, 1);
					command.Append(")");
					cmd.CommandText = command.ToString();

					for (int ind = 0; ind < colNames.Length; ind++)
						cmd.Set(ind, colValues[ind]);

					pk[0] = cmd.ExecuteInsert("pk");
				}
				// test read each var type
				byte[] data = new byte[20];
				using (WrCommand cmd = conn.CreateWrCommand())
				{
					cmd.CommandText = "select * from ALLTYPES where pk = ?";
					cmd.Set(0, pk[0]);
					using (WrDataReader reader = cmd.WrExecuteReader())
					{
						Assert.IsTrue(reader.Read());

						/*
						for (int ind = 0; ind < 16; ind++)
						{
							object obj = reader.GetValue(ind);
							string typ = reader.GetDataTypeName(ind);
							string name = reader.GetName(ind);
							int i = 3;
						}
						*/

						Assert.AreEqual(pk[0], reader.GetInt64(0));
						Assert.AreEqual(colValues[0], reader.GetInt64(1));
						Assert.AreEqual(colValues[1], reader.GetString(2));
						Assert.IsTrue(reader.GetTimestamp(3) > 0);
						Assert.AreEqual(((DateTime)colValues[2]).Date, reader.GetDateTime(4).Date);
						Assert.AreEqual(((DateTime)colValues[3]).Date, reader.GetDateTime(5).Date);
						Assert.AreEqual(colValues[4], reader.GetString(6).Substring(0, 7));
						Assert.AreEqual(colValues[5], reader.GetString(7));
						Assert.AreEqual(4, reader.GetBytes(8, 0, data, 0, 4));
						Assert.IsNotNull(reader.GetString(9)); // what exactly is a CLOB - weird it comes back as a String
						Assert.AreEqual(colValues[8], reader.GetBoolean(10));
						Assert.AreEqual(colValues[9], reader.GetByte(11));
						Assert.AreEqual(colValues[10], reader.GetInt16(12));
						Assert.AreEqual(colValues[11], reader.GetDecimal(13));

						// remaining calls
						Assert.IsFalse(reader.IsDBNull(1));
						Assert.AreEqual("KVARCHAR", reader.GetName(2));
						Assert.AreEqual("TIMESTAMP", reader.GetDataTypeName(3));
						Assert.AreEqual(typeof(String), reader.GetFieldType(6));
						Assert.AreEqual(8, reader.GetOrdinal("KBLOB"));
						Assert.AreEqual(14, reader.FieldCount);
						Assert.AreEqual(colValues[1], reader[2]);
						Assert.AreEqual(colValues[1], reader["kVarChar"]);

						object[] all = new object[14];
						Assert.AreEqual(14, reader.GetValues(all));
						Assert.AreEqual(pk[0], all[0]);
						Assert.AreEqual(colValues[1], all[2]);
					}
				}

				// test select top N
				using (WrCommand cmd = conn.CreateWrCommand())
				{
					cmd.CommandText = "  select   * from ALLTYPES order by pk DESC  top  1  ";
					Assert.AreEqual("select * from (select   * from ALLTYPES order by pk DESC) where ROWNUM <= 1", cmd.CommandText);
					using (WrDataReader reader = cmd.WrExecuteReader())
					{
						Assert.IsTrue(reader.Read());
						Assert.AreEqual(pk[0], reader.GetInt64(0));
					}
				}
			}
		}

//		[Test]
		public void TestLaunch()
		{

			WrOracleVendor vendor = (WrOracleVendor)WrProviderFactories.CreateProvider("Oracle name", null, factoryClass);
			Assert.IsTrue(vendor.RunAdministrator());
		}
	}
}
